package main

import (
	"database/sql"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
	"time"
)

var GLOBAL_DB *gorm.DB

// 数据库连接及选项配置
func GlobalSetting() {
	db,_ := gorm.Open(mysql.New(mysql.Config{
		DSN: "syncuser:123456@tcp(10.211.55.3:54336)/testdb?charset=utf8mb4&parseTime=True&loc=Local",
		DefaultStringSize: 191,
	}), &gorm.Config{
		SkipDefaultTransaction: false,
		NamingStrategy:schema.NamingStrategy{
			TablePrefix: "",	// 表名前缀
			SingularTable: false,	// 使用单数表名
		},
		DisableForeignKeyConstraintWhenMigrating:true,	// 逻辑外键
	})

	sqlDB, err := db.DB()
	if err != nil {
		fmt.Errorf("",err)
	}

	// SetMaxIdleConns 设置空闲连接池中连接的最大数量
	sqlDB.SetMaxIdleConns(10)
	// SetMaxOpenConns 设置打开数据库连接的最大数量。
	sqlDB.SetMaxOpenConns(100)
	// SetConnMaxLifetime 设置了连接可复用的最大时间。
	sqlDB.SetConnMaxLifetime(time.Hour)
	GLOBAL_DB = db
}

type User struct {
	ID           uint
	Name         string
	Email        *string
	Age          uint8
	Birthday     time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

// 创建user表
func CreateUser()  {
	GLOBAL_DB.AutoMigrate(&User{})
}

func main()  {
	// 数据库链接
	GlobalSetting()

	// 创建user表
	CreateUser()

}


type Model struct {
	ID   uint `gorm:"primary_key"`
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt *time.Time
}


type User struct {
	gorm.Model
	Name string
}

func mains() {
	//db, _ := gorm.Open("mysql", "root:123456@gorm?192.168.73.103:3306/gormtest?charset=utf8")
	db, _ := gorm.Open("mysql", "root:123456@tcp(192.168.73.103:3306)/gormtest?charset=utf8mb4&parseTime=True&loc=Local")

	defer db.Close()

	//创建表名users
	db.CreateTable(&User{})

	//更新表，有则更新，无则创建
	db.AutoMigrate(&User{})

	//插入更新数据，有则更新，无则插入
	user := User{Name: "aa"}
	db.Save(&user)

	//插入数据，有则报错
	user := User{Name: "bb"}
	db.Create(&user)
	println(user.ID)

	//NewRecord方法用于判断某个对象是否可以作为新纪录插入
	if(db.NewRecord(&user)){
		db.Create(&user)
	}

	//更改数据
	db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

	// 使用组合条件批量更新单个属性
	db.Model(&user).Where("name= ?", "aa").Update("name", "hello")
	//UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND name='aa';

	// 使用`struct`更新多个属性，只会更新这些更改的和非空白字段
	db.Model(&user).Updates(User{Name: "hello", Age: 18})
	//UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

	// 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值
	db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

	//删除数据
	db.Delete(&user)
	//UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

	// 批量删除
	db.Where("name = ?", "aa").Delete(&User{})
	//UPDATE users SET deleted_at="2013-10-29 10:23" WHERE name = 'aa';

	// 软删除的记录将在查询时被忽略
	db.Where("name = 'aa'").Find(&user)
	//SELECT * FROM users WHERE name = 'aa' AND deleted_at IS NULL;

	// 使用Unscoped查找软删除的记录
	db.Unscoped().Where("name = 'aa'").Find(&users)
	//SELECT * FROM users WHERE name = 'aa';

	// 使用Unscoped永久删除记录
	db.Unscoped().Delete(&order)
	//DELETE FROM orders WHERE id=10;

	// 获取第一条记录，按主键排序
	//First 方法，将查询结果的第一条记录回显到传入形参的结构体对象
	db.First(&user)
	//SELECT * FROM users ORDER BY id LIMIT 1;

	// 获取最后一条记录，按主键排序
	//Last 方法，将查询结果的最后一条记录回显到传入形参的结构体对象
	db.Last(&user)
	//SELECT * FROM users ORDER BY id DESC LIMIT 1;

	// 获取所有记录
	//Find 方法，将全部查询结果加入传入的形参slice
	db.Find(&users)
	//SELECT * FROM users;

	// 按主键获取
	db.First(&user, 23)
	//SELECT * FROM users WHERE id = 23 LIMIT 1;

	// 简单SQL
	db.Find(&user, "name = ?", "jinzhu")
	//SELECT * FROM users WHERE name = "jinzhu";

	db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
	//SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

	// Struct
	db.Find(&users, User{Age: 20})
	//SELECT * FROM users WHERE age = 20;

	// Map
	db.Find(&users, map[string]interface{}{"age": 20})
	//SELECT * FROM users WHERE age = 20;

	//Modal方法，在单表查询中，仅为了设定当前查询的表，传入的结构体对象仅用于设定查询表
	db.Modal(&User{}).Find(&users)

	// 获取第一个匹配记录
	db.Where("name = ?", "jinzhu").First(&user)
	//SELECT * FROM users WHERE name = 'jinzhu' limit 1;

	// 获取所有匹配记录
	db.Where("name = ?", "jinzhu").Find(&users)
	//SELECT * FROM users WHERE name = 'jinzhu';

	//获取不是 jinzhu 的所有记录
	db.Where("name <> ?", "jinzhu").Find(&users)
	//select * from users where name <> 'jinzhu';

	// IN
	//查找 name 在 jinzhu,jinzhu 2 里面的数据
	db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
	// select * from users where name in ("jinzhu", "jinzhu 2")

	// LIKE
	//查找 name 匹配到 jin 的所有数据
	db.Where("name LIKE ?", "%jin%").Find(&users)
	//select * from users where name like "%jin%";

	// AND
	//联合查询，查找 name='jinzhu' 且 age>='22' 的所有数据
	db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
	//select * from users where name='jinzhu' and age>='22';

	// Time
	//查找 updated_at 时间在一周以前的所有数据
	db.Where("updated_at > ?", lastWeek).Find(&users)
	//select * from users where datediff(week,updaed_at,getdate())>0;

	//查找最近一周的数据
	db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
	//select * from info users where datediff(week,created_at,getdate())=0

	// Struct
	//使用struct查询时，GORM将只查询那些具有值的字段
	db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
	//SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

	// Map
	db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
	//SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

	// 主键的Slice
	db.Where([]int64{20, 21, 22}).Find(&users)
	//SELECT * FROM users WHERE id IN (20, 21, 22);



	//Not查询
	//通过Not方法构建取非的查询
	db.Not("name", "jinzhu").First(&user)
	//SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

	// Not In
	db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
	//SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

	// Not In slice of primary keys
	db.Not([]int64{1,2,3}).First(&user)
	//SELECT * FROM users WHERE id NOT IN (1,2,3) limit 1;

	db.Not([]int64{}).First(&user)
	//SELECT * FROM users limit 1;

	// Plain SQL
	db.Not("name = ?", "jinzhu").First(&user)
	//SELECT * FROM users WHERE NOT(name = "jinzhu") limit 1;

	// Struct
	db.Not(User{Name: "jinzhu"}).First(&user)
	//SELECT * FROM users WHERE name <> "jinzhu" limit 1;


	//Or查询
	//Or方法返回符合前一个查询条件或符合当前查询条件的复合条件
	db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
	//SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

	// Struct
	db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
	//SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

	// Map
	db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
	//SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

	//查询链
	//多个查询条件可以直接拼接构建复合条件
	db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users)
	//SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';

	db.Where("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)
	//select * from users where role="admin" or role="super_admin" and name<>"jinzhu"

	//Select字段
	//通过Select方法进行部分字段的查询
	db.Select("name, age").Find(&users)
	//SELECT name, age FROM users;

	db.Select([]string{"name", "age"}).Find(&users)
	//SELECT name, age FROM users;

	db.Table("users").Select("COALESCE(age,?)", 42).Rows()
	//SELECT COALESCE(age,'42') FROM users;

	//Order排序
	//通过Order方法对返回结果进行排序
	db.Order("age desc, name").Find(&users)
	//SELECT * FROM users ORDER BY age desc, name;

	// Multiple orders
	db.Order("age desc").Order("name").Find(&users)
	//SELECT * FROM users ORDER BY age desc, name;

	// ReOrder
	db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
	//SELECT * FROM users ORDER BY age desc; (users1)
	//SELECT * FROM users ORDER BY age; (users2)

	//Limit
	db.Limit(3).Find(&users)
	SELECT * FROM users LIMIT 3;

	// Cancel limit condition with -1
	db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
	//SELECT * FROM users LIMIT 10; (users1)
	//SELECT * FROM users; (users2)

	//Offset
	db.Offset(3).Find(&users)
	//SELECT * FROM users OFFSET 3;

	// Cancel offset condition with -1
	db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
	//SELECT * FROM users OFFSET 10; (users1)
	//SELECT * FROM users; (users2)

	//Count
	//count方法返回结果条数
	db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
	//SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

	db.Table("deleted_users").Count(&count)
	//SELECT count(*) FROM deleted_users;

	//Join
	//通过Join方法进行多表查询
	db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id")



	//因为多表查询结果与实体类不对应，所以数据回显要通过Scan方法回显到任意定义结构体
	//Scan
	//Scan方法将结果扫描到另一个结构中。比如
	type User struct{}
	type Email struct{}
	type result struct{
		User
		Email
	}

	user := User{}
	db.Modal(&User{}).Where("1 = 1").Scan(&user)

	res := make([]Result,1)
	db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&res)


	//Scopes
	//通过Scopes可以将Where语句封装为方法来使用，动态添加参数
	func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
		return func (db *gorm.DB) *gorm.DB {
			return db.Scopes(AmountGreaterThan1000).Where("status in (?)", status)
		}
	}
	db.Scopes(OrderStatus([]string{"paid", "shipped"})).Find(&orders)
	// 查找所有付费，发货订单

	//关联结构
	//一对一
	//默认使用主键作为外键，外键默认命名为 (关联结构体类型名称+关联结构体主键属性名称)
	// `User`属于`Profile`, `ProfileID`为外键
	type User struct {
		gorm.Model
		Profile   Profile
		ProfileID int
	}

	type Profile struct {
		gorm.Model
		Name string
	}

	db.Model(&user).Related(&profile)
	//SELECT * FROM profiles WHERE id = 111; // 111是user的外键ProfileID

	//通过配置ForeignKey指定该关联属性对应在本结构体的外键
	//通过配置AssociationForeignKey指定该关联属性在其关联结构体的外键属性
	type Profile struct {
		gorm.Model
		Refer string
		Name  string
	}

	type User struct {
		gorm.Model
		Profile   Profile `gorm:"ForeignKey:ProfileID;AssociationForeignKey:Refer"`
		ProfileID int
	}

	//一对多
	//与一对一写法类似
	// User 包含多个 emails, UserID 为外键
	type User struct {
		gorm.Model
		Emails   []Email
	}

	type Email struct {
		gorm.Model
		Email   string
		UserID  uint
	}

	db.Model(&user).Related(&emails)
	//SELECT * FROM emails WHERE user_id = 111; // 111 是 user 的主键

	//多对多关系必须由中间表维护，通过many2many属性配置中间表名称
	type CustomizePerson struct {
		IdPerson string             `gorm:"primary_key:true"`
		Accounts []CustomizeAccount `gorm:"many2many:PersonAccount;ForeignKey:IdPerson;AssociationForeignKey:IdAccount"`
	}

	type CustomizeAccount struct {
		IdAccount string `gorm:"primary_key:true"`
		Name      string
	}


	//多态关联
	//比较特殊的还支持多个结构与某一个结构的同一属性进行关联
	//多态属性和多对多显式不支持，并且会抛出错误。
	type Cat struct {
		Id    int
		Name  string
		Toy   Toy `gorm:"polymorphic:Owner;"`
	}

	type Dog struct {
		Id   int
		Name string
		Toy  Toy `gorm:"polymorphic:Owner;"`
	}

	type Toy struct {
		Id        int
		Name      string
		OwnerId   int
		OwnerType string
	}

	//关联更新
	//当保存的实体类包含关联对象时，则会save该关联对象，比如下面代码，从数据库查出user对象，在保存car时，因为car的关联对象owners有值，关联关系和对应的user对象都被修改，名称更新为yyt
	type User struct {
		gorm.Model
		Name string
		A    int
		Cars []Car `gorm:"many2many:car_user;ForeignKey:ID;AssociationForeignKey:ID"`
	}
	type Car struct {
		gorm.Model
		Num    string
		Owners []User `gorm:"many2many:car_user;ForeignKey:ID;AssociationForeignKey:ID"`
	}

	test := User{}
	db.Model(&User{}).Where("1 = 1").First(&test)
	println(test.ID)
	test.Name = "yyt"
	db.Save(&Car{
		Num:    "sssfs",
		Owners: []User{test},
	})


	//如果不想开启该关联更新有两个方式，如下是实时设定当前操作不进行关联更新的
	db.Set("gorm:save_associations", false).Create(&user)

	//另一种方式是在定义结构体的tag里设定save_associations:false来指定不进行关联更新
	type User struct {
		gorm.Model
		Name      string
		CompanyID uint
		Company   Company `gorm:"save_associations:false"`
	}

	type Company struct {
		gorm.Model
		Name string
	}


	//关联查询
	//在关联查询中，db.Modal接收的参数不再只是为了确定表，其必须是一个设定了主键的对象，否则会报错
	//关联查询查询该主键关联的其他表的数据

	//Relative
	//Relative提供关联关系的查询功能
	// User 包含多个 emails, UserID 为外键
	type User struct {
		gorm.Model
		Emails   []Email
		Car Car
	}

	type Email struct {
		gorm.Model
		Email   string
		UserID  uint
	}

	type Car struct {
		gorm.Model
		UserID  uint
	}

user : = User{}
	user.ID = 111
	emails := make([]Email,1)
	db.Model(&user).Related(&emails)
	//SELECT * FROM emails WHERE user_id = 111; // 111 是 user 的主键
	car := Car{}
	db.Model(&user).Related(&car)
	//SELECT * FROM cars WHERE user_id = 111; // 111 是 user 的主键


	//Association
	//Association提供关联关系的各种查询、维护功能
	// 开始关联模式
	var user User
	user.ID = 1
	db.Model(&user).Association("Languages")
	// `user`是源，它需要是一个有效的记录（包含主键）
	// `Languages`是关系中源的字段名。
	// 如果这些条件不匹配，将返回一个错误，检查它：
	// db.Model(&user).Association("Languages").Error


	// Query - 查找所有相关关联
	db.Model(&user).Association("Languages").Find(&languages)


	// Append - 添加新的many2many, has_many关联, 会替换掉当前 has_one, belongs_to关联
	db.Model(&user).Association("Languages").Append([]Language{languageZH, languageEN})
	db.Model(&user).Association("Languages").Append(Language{Name: "DE"})


	// Delete - 删除源和传递的参数之间的关系，不会删除这些参数
	db.Model(&user).Association("Languages").Delete([]Language{languageZH, languageEN})
	db.Model(&user).Association("Languages").Delete(languageZH, languageEN)


	// Replace - 使用新的关联替换当前关联
	db.Model(&user).Association("Languages").Replace([]Language{languageZH, languageEN})
	db.Model(&user).Association("Languages").Replace(Language{Name: "DE"}, languageEN)


	// Count - 返回当前关联的计数
	db.Model(&user).Association("Languages").Count()


	// Clear - 删除源和当前关联之间的关系，不会删除这些关联
	db.Model(&user).Association("Languages").Clear()


}




